package com.fy.tt.dao.impl;

import com.fy.toolhelper.db.BaseDaoImpl;
import com.fy.tt.dao.WordDao;
import com.fy.tt.entity.Word;
import com.fy.tt.entity.query.SemanticQuery;
import com.fy.tt.entity.query.WordQuery;
import com.fy.tt.entity.vo.WordAndSemanticVo;
import com.fy.tt.entity.vo.WordVo;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class WordDaoImpl extends BaseDaoImpl<Word> implements WordDao {

    @Override
    public List<WordVo> getAllWords(Connection connection) throws SQLException {
        String sql = "select w.*,p.band_name from bls_word w left join bls_parser p on w.band_obj_id=p.band_obj_id";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        ArrayList<WordVo> words = new ArrayList<>();
        while(rs.next()){
            WordVo word = new WordVo(rs);
            words.add(word);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return words;
    }


    @Override
    public int deleteWord(Connection connection, int id) throws SQLException {
        String sql = "delete from bls_word where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, id);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }


    @Override
    public List<Word> getPageWords(Connection connection, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "select * from bls_word limit ?,?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Word> words = new ArrayList<>();
        while(rs.next()){
            Word word = new Word(rs);
            words.add(word);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return words;
    }

    @Override
    public List<WordVo> getConditionPageWords(Connection connection, WordQuery wordQuery, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "select w.*,p.band_name from bls_word w left join bls_parser p on w.band_obj_id=p.band_obj_id where 1 = 1 ";

        if(!StringUtils.isEmpty(wordQuery.getName())){
            sql+=" and ( w.name like '%" + wordQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + wordQuery.getName() + "%'";
            sql+=" or w.description like '%" + wordQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(wordQuery.getIsTypeIn())){
            sql+=" and w.is_type_in = " + wordQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(wordQuery.getBandObjId())){
            sql+=" and w.band_obj_id = " + wordQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(wordQuery.getIsRange())){
            sql+=" and w.is_range = " + wordQuery.getIsRange();
        }

        if(StringUtils.isEmpty(wordQuery.getName())
                && StringUtils.isEmpty(wordQuery.getIsTypeIn())
                && StringUtils.isEmpty(wordQuery.getBandObjId())
                && StringUtils.isEmpty(wordQuery.getIsRange())){
            sql = "select w.*,p.band_name from bls_word w left join bls_parser p on w.band_obj_id=p.band_obj_id limit ? , ? ";
        }else{
            sql += " limit ? , ?";
        }

        System.out.println("sql = " + sql);

        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<WordVo> words = new ArrayList<>();
        while(rs.next()){
            WordVo word = new WordVo(rs);
            words.add(word);
        }
        System.out.println(words);
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return words;
    }

    @Override
    public int getTotalWord(Connection connection, WordQuery wordQuery) throws SQLException {
        int count = -1;
        String sql = "select w.*,p.band_name from bls_word w left join bls_parser p on w.band_obj_id=p.band_obj_id where 1 = 1 ";
        if(!StringUtils.isEmpty(wordQuery.getName())){
            sql+=" and ( w.name like '%" + wordQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + wordQuery.getName() + "%'";
            sql+=" or w.description like '%" + wordQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(wordQuery.getIsTypeIn())){
            sql+=" and w.is_type_in = " + wordQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(wordQuery.getBandObjId())){
            sql+=" and w.band_obj_id = " + wordQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(wordQuery.getIsRange())){
            sql+=" and w.is_range = " + wordQuery.getIsRange();
        }

        if(StringUtils.isEmpty(wordQuery.getName())
                && StringUtils.isEmpty(wordQuery.getIsTypeIn())
                && StringUtils.isEmpty(wordQuery.getBandObjId())
                && StringUtils.isEmpty(wordQuery.getIsRange())){
            sql = "select w.*,p.band_name from bls_word w left join bls_parser p on w.band_obj_id=p.band_obj_id ";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        rs.last();
        count = rs.getRow();
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return count;
    }

    @Override
    public List<WordAndSemanticVo> getWordSemantic(Connection connection) throws SQLException {
        ArrayList<WordAndSemanticVo> wordAndSemanticVos = new ArrayList<>();
        String sql = "SELECT w.*,s.* from bls_word w LEFT JOIN bls_semantic s on w.id = s.word_id WHERE w.is_type_in = 1";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        while(rs.next()){
            WordAndSemanticVo wordAndSemanticVo = new WordAndSemanticVo(rs);
            wordAndSemanticVos.add(wordAndSemanticVo);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return wordAndSemanticVos;
    }

    @Override
    public List<WordAndSemanticVo> getAllWordSemantic(Connection connection) throws SQLException {
        ArrayList<WordAndSemanticVo> wordAndSemanticVos = new ArrayList<>();
        String sql = "SELECT w.*,s.* from bls_word w LEFT JOIN bls_semantic s on w.id = s.word_id";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        while(rs.next()){
            WordAndSemanticVo wordAndSemanticVo = new WordAndSemanticVo(rs);
            wordAndSemanticVos.add(wordAndSemanticVo);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return wordAndSemanticVos;
    }

    @Override
    public List<WordAndSemanticVo> getConditionPageAllWordSemantic(Connection connection, SemanticQuery semanticQuery, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "SELECT w.*,s.*,p.band_name FROM bls_word w LEFT JOIN bls_semantic s on w.id = s.word_id LEFT JOIN bls_parser p ON w.band_obj_id=p.band_obj_id WHERE 1 = 1";

        if(!StringUtils.isEmpty(semanticQuery.getName())){
            sql+=" and ( w.name like '%" + semanticQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + semanticQuery.getName() + "%'";
            sql+=" or w.description like '%" + semanticQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsTypeIn())){
            sql+=" and w.is_type_in = " + semanticQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(semanticQuery.getBandObjId())){
            sql+=" and w.band_obj_id = " + semanticQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql+=" and w.is_range = " + semanticQuery.getIsRange();
        }

        if(StringUtils.isEmpty(semanticQuery.getName())
                && StringUtils.isEmpty(semanticQuery.getIsTypeIn())
                && StringUtils.isEmpty(semanticQuery.getBandObjId())
                && StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql = "SELECT w.*,s.*,p.band_name FROM bls_word w LEFT JOIN bls_semantic s on w.id = s.word_id LEFT JOIN bls_parser p ON w.band_obj_id=p.band_obj_id LIMIT ? , ? ";
        }else{
            sql += " limit ? , ?";
        }

        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<WordAndSemanticVo> wordAndSemanticVos = new ArrayList<>();
        while(rs.next()){
            WordAndSemanticVo wordAndSemanticVo = new WordAndSemanticVo(rs);
            wordAndSemanticVos.add(wordAndSemanticVo);
        }
        System.out.println(wordAndSemanticVos);
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return wordAndSemanticVos;
    }

    @Override
    public List<WordAndSemanticVo> getConditionPageWordSemanticDeveloped(Connection connection, SemanticQuery semanticQuery, Integer pageSize, Integer current) throws SQLException {
        int start = pageSize * (current - 1);
        String sql = "SELECT w.*,s.*,p.band_name FROM bls_semantic s LEFT JOIN bls_word w on w.id = s.word_id LEFT JOIN bls_parser p ON w.band_obj_id=p.band_obj_id WHERE w.is_type_in = 1 ";

        if(!StringUtils.isEmpty(semanticQuery.getName())){
            sql+=" and ( w.name like '%" + semanticQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + semanticQuery.getName() + "%'";
            sql+=" or w.description like '%" + semanticQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsTypeIn())){
            sql+=" and w.is_type_in = " + semanticQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(semanticQuery.getBandObjId())){
            sql+=" and w.band_obj_id = " + semanticQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql+=" and w.is_range = " + semanticQuery.getIsRange();
        }

        if(StringUtils.isEmpty(semanticQuery.getName())
                && StringUtils.isEmpty(semanticQuery.getIsTypeIn())
                && StringUtils.isEmpty(semanticQuery.getBandObjId())
                && StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql = "SELECT w.*,s.*,p.band_name FROM bls_semantic s LEFT JOIN bls_word w on w.id = s.word_id LEFT JOIN bls_parser p ON w.band_obj_id=p.band_obj_id WHERE w.is_type_in = 1 LIMIT ? , ? ";
        }else{
            sql += " limit ? , ?";
        }

        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<WordAndSemanticVo> wordAndSemanticVos = new ArrayList<>();
        while(rs.next()){
            WordAndSemanticVo wordAndSemanticVo = new WordAndSemanticVo(rs);
            wordAndSemanticVos.add(wordAndSemanticVo);
        }
        System.out.println(wordAndSemanticVos);
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return wordAndSemanticVos;
    }

    public WordDaoImpl() throws Exception {
        super();
    }
}
